package com.ece.friendly.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.ece.friendly.jdbcConnection.JdbcConnection;
import com.ece.friendly.model.Alerte;
import com.ece.friendly.model.Repondre;

@Service
public class AlerteDaoImp implements AlerteDao{

	@Autowired
	private JdbcConnection jdbcConnection;
	
	@Override
	public boolean addAlerte(String comment, String subject,String looker,long date) {
		try {
			if(jdbcConnection.getStat().executeUpdate("insert into alerte values (NULL ,'"+new Timestamp(date)+"','"+comment+"','"+subject+"','"+looker+"')")>0)
			{
				jdbcConnection.getConnection().commit();
				return true;
			}
			else return false;
        
		}
		catch (SQLException e1) {
			return false;
		}
	}

	@Override
	public List<Alerte> getLastAlert(int limit) {
		try {
			ArrayList<Alerte> array=new ArrayList<Alerte>();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			ResultSet resultat = jdbcConnection.getStat().executeQuery("SELECT * FROM `alerte` order by `date_create` desc limit "+limit+";");
			while(resultat.next()){
				Alerte alerte=new Alerte(Integer.parseInt(resultat.getString(1)),dateFormat.parse(resultat.getString(2)),resultat.getString(3),resultat.getString(4),resultat.getString(5));
				array.add(alerte);
			}	
			
			return array;
		} catch (SQLException | NumberFormatException | ParseException e) {
			e.printStackTrace();return null;
		}
	}

	@Override
	public ArrayList<ArrayList<Object>> getAlertes(String login) {
		try {
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			
			ResultSet resultat = jdbcConnection.getStat().executeQuery("select id,date_create,comment,subject,looker,GROUP_CONCAT(stylie),GROUP_CONCAT(reponse),GROUP_CONCAT(date_reponse)  from alerte left outer join repondre on repondre.alerte=alerte.id where looker='"+login+"' group by alerte.id order by date_create desc;");
			Alerte alerte=null;ArrayList<ArrayList<Object>> alertes=new ArrayList<ArrayList<Object>>();
			ArrayList<Object> alerteDto=null;
			while(resultat.next()){
				alerteDto =new ArrayList<Object>();
				alerte=new Alerte(Integer.parseInt(resultat.getString(1)), dateFormat.parse(resultat.getString(2)),resultat.getString(3) ,resultat.getString(4),resultat.getString(5));
				alerteDto.add(alerte);
				if(resultat.getString(6)!=null && !resultat.getString(6).isEmpty())
				{
					String[] stylies=resultat.getString(6).split(",");
					String[] response=resultat.getString(7).split(",");
					String[] date_rep=resultat.getString(8).split(",");
					for(int i=0;i<stylies.length;i++)
						alerteDto.add(new Repondre(dateFormat.parse(date_rep[i]), stylies[i], alerte.getId(), response[i]));
				}
				alertes.add(alerteDto);
			}
			return alertes;
		} catch (SQLException | NumberFormatException | ParseException e) {
			e.printStackTrace();return null;
		}
	}

	@Override
	public ArrayList<ArrayList<Object>> getAllAlertes() {
		try {
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			
			ResultSet resultat = jdbcConnection.getStat().executeQuery("select id,date_create,comment,subject,looker,GROUP_CONCAT(stylie),GROUP_CONCAT(reponse),GROUP_CONCAT(date_reponse)  from alerte left outer join repondre on repondre.alerte=alerte.id group by alerte.id order by date_create desc;");
			Alerte alerte=null;ArrayList<ArrayList<Object>> alertes=new ArrayList<ArrayList<Object>>();
			ArrayList<Object> alerteDto=null;
			while(resultat.next()){
				alerteDto =new ArrayList<Object>();
				alerte=new Alerte(Integer.parseInt(resultat.getString(1)), dateFormat.parse(resultat.getString(2)),resultat.getString(3) ,resultat.getString(4),resultat.getString(5));
				alerteDto.add(alerte);
				if(resultat.getString(6)!=null && !resultat.getString(6).isEmpty())
				{
					String[] stylies=resultat.getString(6).split(",");
					String[] response=resultat.getString(7).split(",");
					String[] date_rep=resultat.getString(8).split(",");
					for(int i=0;i<stylies.length;i++)
						alerteDto.add(new Repondre(dateFormat.parse(date_rep[i]), stylies[i], alerte.getId(), response[i]));
				}
				alertes.add(alerteDto);
			}
			return alertes;
		} catch (SQLException | NumberFormatException | ParseException e) {
			e.printStackTrace();return null;
		}
	}

	@Override
	public boolean repondreAlerte(String comment) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Alerte getAlerte(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ArrayList<Object> getAlerte(String looker,long date) {
		try {
			ArrayList<Object> array=new ArrayList<Object>();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			ResultSet resultat = jdbcConnection.getStat().executeQuery("SELECT * FROM Alerte where looker='"+looker+"' and date_create='"+new Timestamp(date)+"';");
			resultat.next();
			Alerte alerte=new Alerte(Integer.parseInt(resultat.getString(1)),dateFormat.parse(resultat.getString(2)),resultat.getString(3),resultat.getString(4),resultat.getString(5));
			array.add(alerte);
			
			resultat = jdbcConnection.getStat().executeQuery("SELECT * FROM Repondre where alerte="+alerte.getId()+" order by date_reponse;");
			while(resultat.next()){
				array.add(new Repondre(dateFormat.parse(resultat.getString(1)), resultat.getString(2),Integer.parseInt(resultat.getString(3)), resultat.getString(4)));
			}
			
			
			return array;
		} catch (SQLException | NumberFormatException | ParseException e) {
			e.printStackTrace();return null;
		}
	}

	@Override
	public boolean repondre(String comment, int alerte, String stylie,long date) 
	{
		try {
			PreparedStatement pstmt = jdbcConnection.getConnection().prepareStatement("insert into repondre values ('"+new Timestamp(date)+"','"+stylie+"',"+alerte+",?)");
			pstmt.setString(1, comment);
			if(pstmt.executeUpdate()>0)
				{
					jdbcConnection.getConnection().commit();
					return true;
				}
				else return false;
	        
			}
		catch (SQLException e1) {
			return false;
		}
		
	}

	@Override
	public Repondre getRepondre(int alerte, long date) {
		try {
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			ResultSet resultat = jdbcConnection.getStat().executeQuery("SELECT * FROM Repondre where alerte="+alerte+" and date_reponse='"+new Timestamp(date)+"';");
			resultat.next();
			return new Repondre(dateFormat.parse(resultat.getString(1)),resultat.getString(2),Integer.parseInt(resultat.getString(3)),resultat.getString(4));
			
			
		} catch (SQLException | NumberFormatException | ParseException e) {
			e.printStackTrace();return null;
		}
	}

}
